﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using DbAdapter.sqldbAdapter;
using System.Data.SqlClient;

namespace DAO.WaiGaoQiao
{
    public class DataAccessWork_ArticleBring
    {
     
        /// <summary>
        /// 获取要领取的物品
        /// </summary>
        /// <param name="personNo"></param>
        /// <param name="personName"></param>
        /// <returns></returns>
        public DataSet GetBringUser(string personNo, string personName)
        {
            sqldb db = new sqldb();
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat("EXEC  Proc_Work_ArticleBring '{0}' ", personNo);
            return db.ExecuteDataset(GetConntionString.conntionstring(), CommandType.Text, sb.ToString());
        }
      
        
        /// <summary>
        /// 返回pid
        /// </summary>
        /// <param name="personNo"></param>
        /// <param name="personName"></param>
        /// <returns></returns>
        public int RtnPid(string personNo)
        {
            try
            {
                using (SqlConnection conn = new SqlConnection(GetConntionString.conntionstring()))
                {
                    sqldb db = new sqldb();
                    string query = "SELECT pid  FROM FILE_PERSONNEL_VIEW where 1=1 ";
                    if (!string.IsNullOrEmpty(personNo))
                    {
                        query += "and cardserial='" + personNo + "'";
                        return Convert.ToInt32(db.ExecuteDataset(conn, CommandType.Text, query).Tables[0].Rows[0][0]);
                    }
                    else
                    {
                        return 0;
                    }

                    
                }
            }
            catch 
            {

                return 0;
            }
        }
        /// <summary>
        /// 返回序列号
        /// </summary>
        /// <param name="personNo"></param>
        /// <param name="personName"></param>
        /// <returns></returns>
        public string RtnSerial(string personNo)
        {
            try
            {
                using (SqlConnection conn = new SqlConnection(GetConntionString.conntionstring()))
                {
                    sqldb db = new sqldb();
                    string query = "SELECT cardserial  FROM FILE_PERSONNEL_VIEW where 1=1 ";
                    if (!string.IsNullOrEmpty(personNo))
                    {
                        query += "and personnelid='" + personNo + "'";
                        return Convert.ToString(db.ExecuteDataset(conn, CommandType.Text, query).Tables[0].Rows[0][0]);
                    }
                    else
                    {
                        return "0";
                    }


                }
            }
            catch
            {

                return "0";
            }
        }
        /// <summary>
        /// 返回pid
        /// </summary>
        /// <param name="personNo"></param>
        /// <param name="personName"></param>
        /// <returns></returns>
        public int RtnPidForPersonNo(string personNo)
        {
            try
            {
                using (SqlConnection conn = new SqlConnection(GetConntionString.conntionstring()))
                {
                    sqldb db = new sqldb();
                    string query = "SELECT pid  FROM FILE_PERSONNEL_VIEW where 1=1 ";
                    if (!string.IsNullOrEmpty(personNo))
                    {
                        query += "and personnelid='" + personNo + "'";
                        return Convert.ToInt32(db.ExecuteDataset(conn, CommandType.Text, query).Tables[0].Rows[0][0]);
                    }
                    else
                    {
                        return 0;
                    }


                }
            }
            catch
            {

                return 0;
            }
        }
        /// <summary>
        /// 返回personNo
        /// </summary>
        /// <param name="pid"></param>
        /// <returns></returns>
        public string RtnPersonNo(string pid)
        {
            try
            {
                using (SqlConnection conn = new SqlConnection(GetConntionString.conntionstring()))
                {
                    sqldb db = new sqldb();
                    string query = "SELECT personnelid  FROM FILE_PERSONNEL_VIEW where 1=1 ";
                    if (!string.IsNullOrEmpty(pid))
                    {
                        query += "and pid='" + pid + "'";
                        return Convert.ToString(db.ExecuteDataset(conn, CommandType.Text, query).Tables[0].Rows[0][0]);
                    }
                    else
                    {
                        return "0";
                    }

                   
                }
            }
            catch 
            {

                return "0";
            }
        }
        /// <summary>
        /// 返回用户姓名
        /// </summary>
        /// <param name="pid"></param>
        /// <returns></returns>
        public string RtnPersonName(string personNo)
        {
            try
            {
                using (SqlConnection conn = new SqlConnection(GetConntionString.conntionstring()))
                {
                    sqldb db = new sqldb();
                    string query = "SELECT [pName]  FROM FILE_PERSONNEL_VIEW where 1=1 ";
                    if (!string.IsNullOrEmpty(personNo))
                    {
                        query += "and personnelid='" + personNo + "'";
                        return Convert.ToString(db.ExecuteDataset(conn, CommandType.Text, query).Tables[0].Rows[0][0]);
                    }
                    else
                    {
                        return "";
                    }


                }
            }
            catch
            {

                return "";
            }
        }

        /// <summary>
        /// 根据条件查询领取的物资
        /// </summary>
        /// <returns></returns>
        public DataSet GetAllWork_ArticleBring(string Condition, int pagesize, int startindex)
        {
            using (SqlConnection conn = new SqlConnection(GetConntionString.conntionstring()))
            {
                sqldb db = new sqldb();
                int startrow = pagesize * (startindex - 1);
                int endrow = pagesize * startindex + 1;
                string query = "select B.ID,B.Article_ID,B.Article_Unit,B.BringCount,B.BringTime,B.PersonNo,B.PersonName,B.Remark from (select ROW_NUMBER() OVER(ORDER BY A.ID DESC) rownumber,"
                             + "[ID] ,[Article_ID] ,[Article_Unit],[BringCount] ,[BringTime]  ,[PersonNo] ,[PersonName] ,[Remark] from Work_ArticleBring A where 1=1 " + Condition + ") B "
                             + "where B.rownumber > " + startrow.ToString() + " and B.rownumber < " + endrow.ToString();
                return db.ExecuteDataset(conn, CommandType.Text, query);
            }
        }
        /// <summary>
        /// 根据条件查询领取的物资
        /// </summary>
        /// <returns></returns>
        public DataSet GetAllWork_ArticleBring(string condition)
        {
            sqldb db = new sqldb();
            string query = "select [ID] ,[Article_ID] ,[Article_Unit],[BringCount] ,[BringTime]  ,[PersonNo] ,[PersonName] ,[Remark] from Work_ArticleBring where 1=1 " + condition;
            return db.ExecuteDataset(GetConntionString.conntionstring(), CommandType.Text, query);
        }
        /// <summary>
        /// 根据条件查询领取的物资
        /// </summary>
        /// <returns></returns>
        public DateTime GetMaxBringTime(string condition)
        {
            try
            {
                sqldb db = new sqldb();
                string query = "select max([BringTime]) from Work_ArticleBring where 1=1 " + condition;
                return Convert.ToDateTime(db.ExecuteDataset(GetConntionString.conntionstring(), CommandType.Text, query).Tables[0].Rows[0][0]);
            }
            catch 
            {

                return DateTime.MinValue;
            }
        }
        /// <summary>
        /// 获取卡的注册日期
        /// </summary>
        /// <returns></returns>
        public DateTime GetRegTime(string condition)
        {
            try
            {
                sqldb db = new sqldb();
                string query = "SELECT regtime FROM CARD_CARD WHERE Status in (0,2,4)  and PID='" +condition + "'";
                return Convert.ToDateTime(db.ExecuteDataset(GetConntionString.conntionstring(), CommandType.Text, query).Tables[0].Rows[0][0]);
            }
            catch
            {

                return DateTime.MinValue;
            }
        }
        /// <summary>
        /// 保存或修改领取的物资
        /// </summary>
        /// <param name="dmpara"></param>
        /// <returns></returns>
        public int SaveWork_ArticleBring(object[] ArticleType)
        {
            string insertSql = "insert into Work_ArticleBring([Article_ID] ,[Article_Unit],[BringCount] ,[BringTime]  ,[PersonNo] ,[PersonName] ,[Remark],[postid],[clu])" +
                "values('" + ArticleType[0] + "','" + ArticleType[8] + "','" + ArticleType[1] + "','" + ArticleType[2] + "'," +
                "'" + ArticleType[3] + "','" + ArticleType[4] + "','" + ArticleType[5] + "','" + ArticleType[6] + "','" + ArticleType[7] + "')";
            using (SqlConnection conn = new SqlConnection(GetConntionString.conntionstring()))
            {
                sqldb db = new sqldb();
                return db.ExecuteNonQuery(conn, CommandType.Text, insertSql);
            }
        }
        /// <summary>
        /// 根据条件获取所有劳资物品数量
        /// </summary>
        /// <returns></returns>
        public int GetWork_ArticleBringCount(string Condition)
        {
            sqldb db = new sqldb();
            string query = "select count(1) from Work_ArticleBring where 1=1 " + Condition;
            object rowcount = db.ExecuteScalar(GetConntionString.conntionstring(), CommandType.Text, query);
            return Convert.ToInt32(rowcount);
        }
        /// <summary>
        /// 返回岗位编号
        /// </summary>
        /// <param name="personNo"></param>
        /// <param name="personName"></param>
        /// <returns></returns>
        public int RtnPostNoForPersonNo(string personNo)
        {
            try
            {
                sqldb db = new sqldb();
                string query = "select 岗位编号 from dbo.FILE_PERSONNEL_VIEW where personnelid='" + personNo + "'";
                return Convert.ToInt32(db.ExecuteDataset(GetConntionString.conntionstring(), CommandType.Text, query).Tables[0].Rows[0][0]);
            }
            catch
            {

                return  0;
            }
        }


    }
}
